package com.surprise.db.dao.join;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.surprise.db.dao.SimpleDAO;
import com.surprise.db.entity.join.SelectPMHelpMst001Entity;

/**
 * SelectPMHelpMst001.java
 * @author xuling
 * @since 2017年2月19日
 */
@Repository
public class SelectPMHelpMst001 extends SimpleDAO<SelectPMHelpMst001Entity> {
	
	/**
	 * 获取指定商场所有的找资源
	 * @param userid 用户ID
	 * @param type 类型
	 * @param limit limit信息
	 * @return 指定商场的找资源
	 */
	public List<SelectPMHelpMst001Entity> select(Long userid, int type, String limit) {
		String sql = "select pm_help_mst.id, pm_help_mst.userid, user.username as name, " +
					 " user.portrait as portrait, pm_help_mst.content, pm_help_mst.titleimg, " +
					 " pm_help_mst.detailimg, pm_help_mst.addtime, " +
					 " pm_help_mst.address, " +
					 " COUNT(DISTINCT pm_help_like_mst.id) as likeCount, " +
					 " COUNT(DISTINCT pm_help_comment_mst.id) as commentCount, " +
					 " COUNT(DISTINCT pm_help_share_mst.id) as shareCount, " +
					 " COUNT(DISTINCT likeB.id) as userLikeCount, " +
					 " COUNT(DISTINCT user_relation.id) as userRelationCount " +
					 " from pm_help_mst " +
					 " LEFT JOIN user on pm_help_mst.userid = user.id " +
					 " LEFT JOIN pm_help_like_mst on pm_help_mst.id = pm_help_like_mst.helpid " + 
					 " and pm_help_like_mst.deleteflg = 0" +
					 " LEFT JOIN pm_help_comment_mst on pm_help_mst.id = pm_help_comment_mst.helpid " +
					 " LEFT JOIN pm_help_share_mst on pm_help_mst.id = pm_help_share_mst.helpid " + 
					 " and pm_help_share_mst.deleteflg = 0 " + 
					 " LEFT JOIN pm_help_like_mst likeB on pm_help_mst.id = likeB.helpid " + 
					 " and likeB.userid = ? and likeB.deleteflg = 0 " +
					 " LEFT JOIN user_relation on user_relation.userid = ? " +
					 " and user_relation.friendid = pm_help_mst.userid " +
					 " and user_relation.deleteflg = 0 " +
					 " WHERE pm_help_mst.type = ? " +
					 " GROUP BY" +
					 " pm_help_mst.id, pm_help_mst.userid, " +
					 " pm_help_mst.content, pm_help_mst.titleimg, " +
					 " pm_help_mst.detailimg, pm_help_mst.addtime, " +
					 " pm_help_mst.address " +
					 " ORDER BY pm_help_mst.id desc"
					 + limit;
		return super.findBySql(sql, userid, userid, type);
	}
}
